package com.isky.visual.db.factory;

import com.isky.visual.constant.CommonConstant;
import com.isky.visual.constant.DbConstant;
import com.isky.visual.db.domain.DatabaseConfig;
import com.isky.visual.db.entity.Source;
import com.isky.visual.db.enums.DBType;
import com.isky.visual.exception.GlobalException;
import com.isky.visual.result.CodeMsg;
import com.isky.visual.util.DBUtil;
import com.isky.visual.util.StringUtil;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @version v1.0
 * @ProjectName visual_parent
 * @ClassName OrcleManager
 * @Description Oracle 的相关管理操作
 * @Author wudl
 * @Date DATE{TIME}
 */

public class OracleManager implements IDbManager {
    private Logger logger = LoggerFactory.getLogger(MySqlManager.class);

    Source source;

    public OracleManager() {
    }

    public OracleManager( Source source) {
        this.source = source;
    }

    @Override
    public Connection getConnection() {
        Connection connection = null;
        try {
            //加载数据驱动
            Class.forName(DBType.Oracle.getDriverClass());
            String url = String.format(DBType.Oracle.getConnectionUrlPattern(),
                    source.getDbUrl(),
                    source.getListenPort(),
                    DbConstant.ORCL);
            String user = source.getUserName();
            String password = source.getUserPwd();
            // 连接数据库
            connection = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("数据库获取连接异常" + e.getMessage());
            throw new GlobalException(CodeMsg.DB_CONNECTION_ERROR);
        }
        return connection;
    }

    @Override
    public String getShowColumnSql(String tableName,Source source) {
        /**
         * 此种sql 查询也无法查出Oracle 表字段注释
         * SELECT
         *   DISTINCT(atc.column_name) as column_name,
         *   atc.data_type as column_type,
         *   ucc.comments as column_comment
         * FROM all_tab_cols atc
         * left join user_col_comments ucc
         * on atc.column_name = ucc.column_name
         * where atc.table_name = 'ZTS_USER'
         * and ucc.table_name = 'ZTS_USER';
         */
        return "select * from " + source.getDbName()+ CommonConstant.DOU +tableName + " where rownum = 1";
    }


    @Override
    public String getShowTableSql() {
        String sql = "select adb.object_name as table_name,adb.object_type as table_type," +
                "utc.comments as table_remark from dba_objects adb " +
                "left join user_tab_comments utc on utc.table_name = adb.object_name " +
                "where owner = '"+ source.getDbName()+"' and object_type in ('TABLE','VIEW')";
        return sql;
    }

    @Override
    public String getShowTableSql(Source source) {
        return "SELECT * FROM ALL_TABLES WHERE OWNER = '" + source.getUserName()+"'";
    }

    /**
     * 获取表字段
     *
     * @param con
     * @param sql
     * @return
     */
    @Override
    public List<Map<String, Object>> getTableColumn(Connection con, String sql) throws Exception {
        List<Map<String, Object>> queryList = new ArrayList<>();
        Statement statement = null;
        ResultSet resultSet = null;
        if (null == con || StringUtils.isBlank(sql)) {
            return null;
        }
        try {
            statement = con.createStatement();
            resultSet = statement.executeQuery(sql);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();
            while (resultSet.next()) {
                for (int i = 1; i <= count; i++) {
                    Map<String, Object> rowMap = new HashMap<>(16);
                    rowMap.put("COLUMN_NAME", metaData.getColumnName(i));
                    rowMap.put("namecn", metaData.getColumnLabel(i));
                    queryList.add(rowMap);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new GlobalException(CodeMsg.TABLE_COLUMN_ERROR);
        } finally {
            DBUtil.closeLink(null, statement, resultSet, con);
        }
        return queryList;
    }

    @Override
    public String getSingleTableCountSql(String tableName) {
        return "select count(*) as COUNT  from " + this.source.getDbName() + "."+ tableName;
    }

    @Override
    public String getSingleTableDataSql(String tableName, Integer limit) {
        String count = limit + 1 + "";
        return "select * from " + this.source.getDbName() + "."+ tableName + " where rownum < " +count;
    }
}
